package com.wsj.dao.hibernate;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.SessionFactoryUtils;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.wsj.dao.IHomeSiteAdBannerDao;
import com.wsj.util.page.PageResultSet;
import com.wsj.util.page.PerfectPageResultSet;
import com.wsj.util.tool.Tool;
import com.wsj.web.vo.homeSite.HomeSiteAdBanner;

import java.sql.CallableStatement;

public class HomeSiteAdBannerDaoImpl extends HibernateDaoSupport implements IHomeSiteAdBannerDao {

	public void add(HomeSiteAdBanner hs) throws DataAccessException {
		this.getHibernateTemplate().save(hs);
	}

	public void delete(HomeSiteAdBanner hs) throws DataAccessException {
		this.getHibernateTemplate().delete(hs);
	}

	/** 通过 广告描述ID 加载广告列表 */
	public List loadByDiscriptId(Integer discriptId) throws DataAccessException {
		List list = new ArrayList();
		String sql = "from HomeSiteAdBanner h where 1=1 and h.homeSiteAdBannerDescription.id = ? ";
		list = this.getHibernateTemplate().find(sql,discriptId);
		return list;
	}

	/** 通过 栏目分类ID 加载广告列表 */
	public List loadByNewsClassId(Long newsClassId) throws DataAccessException {
	
		List list = new ArrayList();
		String sql = "";
		if(newsClassId != null){
			sql = " from HomeSiteAdBanner h where 1 = 1 and h.homeSiteNewsClass.id = ?";
			list = this.getHibernateTemplate().find(sql,newsClassId);
		}else{
			sql = " from HomeSiteAdBanner h where 1 = 1 ";
			list = this.getHibernateTemplate().find(sql);
		}
		
		return list;
	}

	public HomeSiteAdBanner loadHsAdBannerById(Integer id) throws DataAccessException {
		Object obj = new Object();
		HomeSiteAdBanner hs = null;
		obj = this.getHibernateTemplate().get(HomeSiteAdBanner.class, id);
		if(obj!=null){
			hs = (HomeSiteAdBanner)obj;
		}
		
		return hs;
	}

	public void update(HomeSiteAdBanner hs) throws DataAccessException {
		this.getHibernateTemplate().saveOrUpdate(hs);
	}

	public PageResultSet loadPageList(Long newsClassId,Integer hsDesId,String title, Integer isImg, Short isLink, Short isJs, Integer page,Integer pageSize) throws DataAccessException {
		PageResultSet pageResultSet = new PageResultSet();
	    PerfectPageResultSet pageResult = new PerfectPageResultSet();
	    // 获取Session
	    Session session = SessionFactoryUtils.getSession(getSessionFactory(),true);

	    // 初始化SQL语句
	    StringBuffer stringBuffer = new StringBuffer(
	            " from HomeSiteAdBanner as hs  where 1=1 ");
	    
	    // 设置每页显示得记录数和开始页
	    pageResult.setPage(page, pageSize);

	    // 参数
	    List<Comparable> list = new ArrayList<Comparable>();
	    
	    // 栏目分类
	    if(newsClassId!=null && !newsClassId.equals(new Long(-1)))
	    {
	        stringBuffer.append(" and hs.homeSiteNewsClass.id = ?");
	        list.add(newsClassId);
	    }
	    
	    // 广告描述
	    if(hsDesId != null){
	    	stringBuffer.append(" and hs.homeSiteAdBannerDescription.id = ?");
	        list.add(hsDesId);	    	
	    }
	    
	    // 广告标题
	    if(title!=null && !title.equals(""))
	    {
	    	title = "%"+title+"%";
	        stringBuffer.append(" and hs.title like ?");
	        
	        list.add(title);
	    }
	    
//	    // 是否是图片 0 否 1 是
//	    if(isImg!=null&&!isImg.equals(new Integer(-1)))
//	    {
//	    	stringBuffer.append(" and hs.isImg = ?");
//	        
//	        list.add(isImg);
//	    }
//	    
//	    // 是否是外连接 0 否 1 是
//	    if(isLink!=null&&!isLink.equals((short)-1))
//	    {
//	    	stringBuffer.append(" and hs.isLink = ?");
//	        
//	        list.add(isLink);
//	    }
//	    
//	    // 是否是JS广告 0 否 1 是
//	    if(isJs!=null&&!isJs.equals((short)-1))
//	    {
//	    	stringBuffer.append(" and hs.isJs = ?");
//	        
//	        list.add(isJs);
//	    }
	   
	    stringBuffer.append(" order by sequence desc, id desc ");

	    // 把集合转换成数组
	    String[] strParams = Tool.toArray(list);
	    
	    pageResultSet = pageResult.pageResultSet(stringBuffer.toString(),session, strParams);
	    
	    return pageResultSet;
	}

	public List loadTopByNewsClassId(long newsClassId, int top) throws DataAccessException {
		String sql=" from HomeSiteAdBanner as h where 1=1 and h.isImg=1 and h.homeSiteNewsClass.id = ?  order by h.sequence desc ";
		Query q=this.getSession().createQuery(sql);
		q.setParameter(0, newsClassId);
		q.setFirstResult(0);
		q.setMaxResults(top);
		return q.list();
	}

	public List loadTopByOnlyNewsClassId(long newsClassId, int top) throws DataAccessException {
		String sql=" from HomeSiteAdBanner as h where 1=1  and h.isImg=1   and h.homeSiteNewsClass.id = ?  order by h.id desc ";
		Query q=this.getSession().createQuery(sql);
		q.setParameter(0, newsClassId);
		q.setFirstResult(0);
		q.setMaxResults(top);
		return q.list();
	}

	/** 查询 符合添加的图片广告，或者 非图片（文字）广告 */
	@SuppressWarnings("unchecked")
	public List<HomeSiteAdBanner> loadTopByNewsClassId(Integer desId,Integer isPic, Integer top) throws DataAccessException {
		List list = null;
		StringBuffer sql = new StringBuffer(" from HomeSiteAdBanner as h where 1 = 1 ");
		if((new Integer(1).equals(isPic))){
			sql.append(" and h.isImg = 1 and h.homeSiteAdBannerDescription.id = :id ");
		}else{
			sql.append(" and h.isImg = 0 and h.homeSiteAdBannerDescription.id = :id ");
		}
		sql.append(" order by h.sequence desc ,h.id desc");
		list = this.getSession().createQuery(sql.toString()).setInteger("id", desId).setMaxResults(top).list();
		if(list != null && list.size()>0){
			return list;
		}
		
		return null;
	}

	
	/***
	 * 通过广告分类名,广告描述名,图片还是文字,是否删除,前几个 ,查询广告列表
	 */
	public List loadHomeSiteAdBannerByClassNameDao(String newsClassName, String homeSiteAdBannerName, Short picOrletter, Short isdel, Integer top) throws DataAccessException 
	{
		
		StringBuffer hql=new StringBuffer(" from HomeSiteAdBanner as ad where ad.homeSiteNewsClass.names =? and ad.homeSiteAdBannerDescription.picName = ? ");
		
		if(picOrletter!=null)
		{
			hql.append(" and ad.isImg = ?");
		}
		hql.append(" order by ad.sequence desc ");
		
		Query q=this.getSession().createQuery(hql.toString());
		q.setParameter(0,newsClassName);
		q.setParameter(1, homeSiteAdBannerName);
		
		if(picOrletter!=null)
		{
			q.setParameter(2, picOrletter);	
		}
		
		q.setMaxResults(top);
		return q.list();
	}

	
	/**  
	 * 参数：广告源ID、广告描述ID、类型，向上或向下
	 * 由于 类型不能为空，若空默认为 向上 0 向上，1向下
	 * */
	public Boolean updateSequence(Integer adId, Integer adDesId, Integer typeId) throws DataAccessException 
	{
		
		Session session = this.getSession();
		Transaction tx   =  session.beginTransaction();
		Connection con = session.connection();
		String sql = "{call proc_updateSequence_ad (?,?,?)}";
		try {
			//con.setAutoCommit(false); 
			CallableStatement pst =  con.prepareCall(sql);
			if(typeId == null){
				typeId = new Integer(0);
			}
			pst.setInt(1,adId);
			pst.setInt(2,typeId);//0向上 ,1向下
			pst.setInt(3, adDesId);
			
			logger.info("\n广告id:"+adId+" 方向:"+typeId+"  描述id:"+adDesId);
			pst.executeUpdate();
			tx.commit();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
			return false;
	}
	
	

}
